import json
from datetime import datetime, timezone
from typing import Optional, Dict, Any

from pydantic import BaseModel, Field, field_serializer, field_validator
from sqlalchemy import (
    Column, Integer, String, bindparam, JSON, DateTime, SmallInteger, text
)
from sqlalchemy.ext.declarative import declarative_base

from schemas.tool.tool_schemas import PageDTO
from tools.db_model_tool import transactional, db_session

Base = declarative_base()


class ScheduledTask(Base):
    __tablename__ = 'tb_scheduled_tasks'
    __table_args__ = {'comment': '调度任务定义表'}

    id = Column(Integer, primary_key=True, autoincrement=True, comment='主键')
    name = Column(String(100), nullable=True, comment='任务名称')
    invoke_target = Column(String(255), nullable=True, comment='要调用的函数：module.func')
    invoke_args = Column(JSON, nullable=True, comment='调用参数：{"args": [...]}')
    invoke_kwargs = Column(JSON, nullable=True, comment='调用参数{"kwargs": {...}}')
    cron_expr = Column(String(100), nullable=True, comment='Cron 表达式')
    interval_seconds = Column(Integer, nullable=True, comment='间隔秒')
    strategy = Column(String(20), nullable=True, comment='执行策略: once | immediate | scheduled | abandon')
    status = Column(SmallInteger, nullable=True, comment='状态：0=禁用/已删除 1=启用 2=已暂停')
    is_del = Column(SmallInteger, default=1, nullable=True, comment='是否删除：0=已删除 1=未删除')
    created_at = Column(DateTime, default=datetime.now(timezone.utc), nullable=True, comment='创建时间')
    updated_at = Column(DateTime, default=datetime.now(timezone.utc), onupdate=datetime.now(timezone.utc),
                        nullable=True, comment='更新时间')


class ScheduledTaskDTO(BaseModel):
    """
    创建/更新调度任务的入参 DTO
    """
    name: Optional[str] = Field(None, description='任务名称')
    invoke_target: Optional[str] = Field(None, description='要调用的函数：module.func')
    invoke_args: Optional[Dict] = Field(None, description='调用参数')
    invoke_kwargs: Optional[Dict] = Field(None, description='调用参数')
    cron_expr: Optional[str] = Field(None, description='Cron 表达式')
    interval_seconds: Optional[int] = Field(None, description='间隔秒')
    strategy: Optional[str] = Field(None, description='执行策略: once | immediate | scheduled | abandon')
    status: Optional[int] = Field(None, description='状态：0=禁用/已删除 1=启用 2=已暂停')

    @field_validator("invoke_args", mode="before")
    def parse_invoke_args(cls, v):
        if isinstance(v, str):
            try:
                return json.loads(v)
            except Exception:
                return {"args": []}
        return v or {"args": []}

    @field_validator("invoke_kwargs", mode="before")
    def parse_invoke_kwargs(cls, v):
        if isinstance(v, str):
            try:
                return json.loads(v)
            except Exception:
                return {"kwargs": {}}
        return v or {"kwargs": {}}


class ScheduledTaskVO(BaseModel):
    id: Optional[int] = None
    name: Optional[str] = None
    invoke_target: Optional[str] = None
    invoke_args: Optional[Dict[str, Any]] = Field(default_factory=dict)  # 默认 args: []
    invoke_kwargs: Optional[Dict[str, dict]] = Field(default_factory=dict)  # 默认 kwargs: {}
    cron_expr: Optional[str] = None
    interval_seconds: Optional[int] = None
    strategy: Optional[str] = None
    status: Optional[int] = None
    is_del: Optional[int] = None
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None
    next_run_time: Optional[datetime] = Field(None, description='下次调度时间')

    @field_validator("invoke_args", mode="before")
    def parse_invoke_args(cls, v):
        if isinstance(v, str):
            try:
                return json.loads(v)
            except Exception:
                return {"args": []}
        v = v or {"args": []}

        # Ensure `timeout` and `max_retries` are lists
        if isinstance(v.get("timeout"), int):
            v["timeout"] = [v["timeout"]]
        if isinstance(v.get("max_retries"), int):
            v["max_retries"] = [v["max_retries"]]
        return v

    @field_validator("invoke_kwargs", mode="before")
    def parse_invoke_kwargs(cls, v):
        if isinstance(v, str):
            try:
                return json.loads(v)
            except Exception:
                return {"kwargs": {}}
        return v or {"kwargs": {}}

    @field_serializer("created_at", "updated_at", "next_run_time", check_fields=False)
    def serialize_datetime(dt: datetime | None) -> str | None:
        return dt.strftime("%Y-%m-%d %H:%M:%S") if dt else None

    class Config:
        from_attributes = True


@transactional
def create_scheduled_task(task_in: ScheduledTaskDTO, db) -> ScheduledTaskVO:
    now = datetime.now(timezone.utc)
    sql = text("""
        INSERT INTO tb_scheduled_tasks (
            name, invoke_target, invoke_args,invoke_kwargs, cron_expr,
            interval_seconds, strategy, status, is_del,
            created_at, updated_at
        ) VALUES (
            :name, :invoke_target, :invoke_args, :invoke_kwargs,:cron_expr,
            :interval_seconds, :strategy, :status, 1,
            :created_at, :updated_at
        )
    """).bindparams(
        bindparam("name"),
        bindparam("invoke_target"),
        bindparam("invoke_args", type_=JSON),
        bindparam("invoke_kwargs", type_=JSON),
        bindparam("cron_expr"),
        bindparam("interval_seconds"),
        bindparam("strategy"),
        bindparam("status"),
        bindparam("created_at"),
        bindparam("updated_at"),
    )

    params = task_in.model_dump(exclude_unset=True)
    params["created_at"] = now
    params["updated_at"] = now

    result = db.execute(sql, params)
    new_id = result.lastrowid

    row = db.execute(
        text("SELECT * FROM tb_scheduled_tasks WHERE id = :id"),
        {"id": new_id}
    ).fetchone()

    return ScheduledTaskVO(**row._mapping)


@db_session
def get_scheduled_task_list(query: PageDTO, db) -> Dict[str, Any]:
    """
    获取带分页、排序、过滤的调度任务列表，并返回总数。
    """
    base_where_clause = "is_del = 1"
    where_clauses = [base_where_clause]
    params: Dict[str, Any] = {}

    # 动态构建 WHERE 条件
    if query.filters:
        for field, value in query.filters.items():
            if value is not None:
                param_name = f"filter_{field}"  # 避免与 LIMIT/OFFSET 冲突
                if isinstance(value, str):
                    where_clauses.append(f"{field} LIKE :{param_name}")
                    params[param_name] = f"%{value.strip()}%"
                elif isinstance(value, dict):
                    where_clauses.append(f"{field} = :{param_name}")
                    params[param_name] = json.dumps(value)
                else:
                    where_clauses.append(f"{field} = :{param_name}")
                    params[param_name] = value

    # 分页
    offset = max((query.page - 1) * query.page_size, 0)
    params["limit"] = query.page_size
    params["offset"] = offset

    # 排序字段校验
    sort_field = query.sort_by or "created_at"
    sort_order = (query.sort_order or "desc").lower()
    valid_columns = ScheduledTaskVO.__annotations__.keys()

    if sort_field not in valid_columns:
        raise ValueError(f"无效的排序字段: {sort_field}")
    if sort_order not in ("asc", "desc"):
        raise ValueError(f"无效的排序方向: {sort_order}")

    # 构建最终 SQL
    where_clause = " AND ".join(where_clauses)
    sql = text(f"""
        SELECT * FROM tb_scheduled_tasks
        WHERE {where_clause}
        ORDER BY {sort_field} {sort_order}
        LIMIT :limit OFFSET :offset
    """)
    count_sql = text(f"""
        SELECT COUNT(*) as total
        FROM tb_scheduled_tasks
        WHERE {where_clause}
    """)

    # 执行查询
    rows = db.execute(sql, params).fetchall()
    total_row = db.execute(count_sql, params).fetchone()
    total = total_row[0] if total_row else 0

    result = [ScheduledTaskVO(**row._mapping) for row in rows]

    return {
        "total": total,
        "result": result
    }


@transactional
def update_scheduled_task(update_data: ScheduledTaskVO, db) -> bool:
    fields = update_data.model_dump(exclude_unset=True)
    if update_data.id is None:
        raise ValueError("更新任务时必须提供 ID")
    if not fields:
        return False

    stmt = text("""
        UPDATE tb_scheduled_tasks
        SET
            name           = COALESCE(:name, name),
            invoke_target  = COALESCE(:invoke_target, invoke_target),
            invoke_args    = COALESCE(:invoke_args, invoke_args),
            invoke_kwargs    = COALESCE(:invoke_kwargs, invoke_kwargs),
            cron_expr      = COALESCE(:cron_expr, cron_expr),
            interval_seconds = COALESCE(:interval_seconds, interval_seconds),
            strategy       = COALESCE(:strategy, strategy),
            status         = COALESCE(:status, status),
            updated_at     = :updated_at
        WHERE id = :id AND is_del = 1
    """).bindparams(
        bindparam("name"),
        bindparam("invoke_target"),
        bindparam("invoke_args", type_=JSON),
        bindparam("invoke_kwargs", type_=JSON),
        bindparam("cron_expr"),
        bindparam("interval_seconds"),
        bindparam("strategy"),
        bindparam("status"),
        bindparam("updated_at"),
        bindparam("id"),
    )

    params = {
        **fields,
        "updated_at": datetime.now(timezone.utc),
        "id": update_data.id
    }
    result = db.execute(stmt, params)
    return result.rowcount > 0


@transactional
def delete_scheduled_tasks(task_ids: list[int], db) -> int:
    """
    批量逻辑删除调度任务
    - 将 is_del 置为 0
    - 更新 updated_at
    :param task_ids: 任务主键列表
    :param db: DB 会话
    :return: 成功删除的条数
    """
    if not task_ids:
        return 0

    sql = text(f"""
           UPDATE tb_scheduled_tasks
           SET is_del = 0,
               updated_at = :updated_at
           WHERE id IN :ids AND is_del = 1
       """)
    # 注意 SQLAlchemy 要求元组格式才识别 IN 子句
    params = {
        "ids": tuple(task_ids),
        "updated_at": datetime.now(timezone.utc),
    }
    result = db.execute(sql, params)
    return result.rowcount


@db_session
def get_by_ids(ids: list[int], db) -> list[ScheduledTaskVO]:
    if not ids:
        return []

    # 把 is_del 改成 0，表示“未删除”
    stmt = (
        text("SELECT * FROM tb_scheduled_tasks "
             "WHERE id IN :ids AND is_del = :is_del")
        .bindparams(
            bindparam("ids", expanding=True),
            bindparam("is_del", expanding=False),
        )
    )
    params = {
        "ids": ids,  # SQLAlchemy 会自动展开成 id IN (:ids_1,…)
        "is_del": 1,  # 0 表示“正常”
    }
    rows = db.execute(stmt, params).fetchall()
    return [ScheduledTaskVO(**r._mapping) for r in rows]
